package org.ru.mse10.cvis_generators;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.text.MessageFormat;

import jxl.Sheet;
import jxl.Workbook;

public class ClSqlGenerator {
	public static void main(String[] args) throws Exception {
		generate();
	}

	public static void generate() throws Exception {
		Workbook workbook = Workbook.getWorkbook(ClSqlGenerator.class.getResourceAsStream("/cvis-codelists.xls"));
		Sheet sheet = workbook.getSheet(0);
		File file = new File("generated/cl_inserts.sql");
		file.createNewFile();
		BufferedWriter out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file), "UTF8"));

		String lineSeparator = System.getProperty("line.separator");

		String insertClQuery = "insert into codelist values({0}, ''{1}'');";
		String insertTranslatedValue = "insert into translated_value values({0}, ''{1}'', _utf8''{2}'');";
		String insertTranslatedValueCodelist = "insert into codelist_translated_value values({0}, {1});";
		String insertCodelistValue = "insert into codelist_value values({0}, ''{1}'', {2});";
		String insertTranslatedCodelistValue = "insert into codelist_value_translated_value values({0}, {1});";

		Long clId = 0L;
		Long tvId = 0L;
		Long cvId = 0L;
		for (int i = 1;; i++) {
			String clCode = sheet.getCell(0, i).getContents();
			if (isEmpty(clCode)) {
				break;
			}
			clId++;
			out.write(MessageFormat.format(insertClQuery, clId, escapeSingleQuote(clCode)) + lineSeparator);
			for (int j = i + 1;; j++) {
				String descrCode = sheet.getCell(1, j).getContents();
				String descrVal = sheet.getCell(2, j).getContents();
				if (isEmpty(descrCode) || isEmpty(descrVal)) {
					break;
				}
				tvId++;
				out.write(MessageFormat.format(insertTranslatedValue, tvId, escapeSingleQuote(descrCode),
						escapeSingleQuote(descrVal)) + lineSeparator);
				out.write(MessageFormat.format(insertTranslatedValueCodelist, clId, tvId) + lineSeparator);
			}

			for (int k = i + 2;; k++) {
				String cvCode = sheet.getCell(3, k).getContents();
				String valueLangCode = sheet.getCell(4, k).getContents();
				String value = sheet.getCell(5, k).getContents();
				if (isEmpty(cvCode) || isEmpty(valueLangCode) || isEmpty(value)) {
					i = k - 1;
					break;
				}
				cvId++;
				out.write(MessageFormat.format(insertCodelistValue, cvId, escapeSingleQuote(cvCode), clId)
						+ lineSeparator);
				tvId++;
				out.write(MessageFormat.format(insertTranslatedValue, tvId, escapeSingleQuote(valueLangCode),
						escapeSingleQuote(value)) + lineSeparator);
				out.write(MessageFormat.format(insertTranslatedCodelistValue, cvId, tvId) + lineSeparator);
			}
		}
		out.flush();
		out.close();
	}

	public static String escapeSingleQuote(String s) {
		return s.replace("'", "''");
	}

	public static boolean isEmpty(String value) {
		if (value != null && value.trim().length() > 0) {
			return false;
		}
		return true;
	}
}
